package telsoft.app.bean;

import telsoft.sql.Database;
import java.sql.PreparedStatement;
import java.util.Vector;
import telsoft.app.bean.TransportBean;
import telsoft.admin.util.AppEntityProcessor;
import telsoft.util.StringUtil;
import telsoft.app.util.PermissionApp;
import telsoft.util.AppException;
import java.sql.ResultSet;
import telsoft.admin.util.AppServer;
import ReportTDK.Report;

public class ReportBean extends AppEntityProcessor {
    private String mstrCity = "";
    private String mstrStartdate = "";
    private String mstrFinishdate = "";
    private String mstrArea_id = "";
    PreparedStatement stmt = null;
    public String mstrTemplate = "";
    String strSQL;
    String SQL;
//    PreparedStatement stmt = null;

    public Vector loadForm() throws Exception {
        try {
            open();
            strSQL = "select area_id,fullname from area where parent_code= ? ";
//	    loadParameter();
            stmt = mcnMain.prepareStatement(strSQL);
            stmt.setString(1, mstrCity);
            return Database.convertToVector(stmt.executeQuery());
        } finally {
            close();
        }
    }
//load thanh pho vao combobox
    public Vector loadcity() throws Exception {
       try {
	   open();
	   strSQL = "select area_code,fullname from area where parent_code is null";
	   Vector vtReturn = Database.executeQuery(getConnection(), strSQL);
	   return vtReturn;//tra ve vector de day ve client
       } finally {
	   close();
       }
   }
public String BuildSQL(){
    return SQL = " select fullname,center_code ,number_trans, number_ok,case "+
		  " when number_trans = '0' then '0.00%' "+
		  " else TO_CHAR(((number_ok / number_trans) * 100),'999.99') || '%' "+
		  " end per from (SELECT   fullname, "+
		  " center_code, "+
		  " nvl((  SELECT   get_sum_map (b.area_id, "+
		   " TO_DATE (?, 'dd/mm/yyyy'),"+
		   " TO_DATE (?, 'dd/mm/yyyy'))"+
		   " FROM   isdn a, area_map_isdn b"+
		   " WHERE       a.reg_status = 1"+
		     " AND b.area_id = c.area_id"+
		     " AND a.imsi LIKE b.imsi_prefix"+
		     " AND b.start_date <= TRUNC (a.reg_date)"+
		     " AND (b.finish_date >= TRUNC (a.reg_date)"+
		     " OR b.finish_date IS NULL)"+
		     " AND TRUNC (a.reg_date) >="+
			    " TO_DATE (?, 'dd/mm/yyyy')"+
		     " AND TRUNC (a.reg_date) <"+
			    " TO_DATE (?, 'dd/mm/yyyy') + 1"+
		     " AND b.area_id IN ("+mstrArea_id+")"+
	  " GROUP BY   b.area_id),0) number_trans,"+
	 " nvl((  SELECT   COUNT ( * )"+
	      " FROM   isdn a, area_map_isdn b"+
	     " WHERE       a.reg_status = 1"+
	     " AND b.area_id = c.area_id"+
		     " AND a.imsi LIKE b.imsi_prefix"+
		     " AND b.start_date <= TRUNC (a.reg_date)"+
		     " AND (b.finish_date >= TRUNC (a.reg_date)"+
		     " OR b.finish_date IS NULL)"+
		     " AND TRUNC (a.reg_date) >="+
			    " TO_DATE (?, 'dd/mm/yyyy')"+
		     " AND TRUNC (a.reg_date) <"+
			    " TO_DATE (?, 'dd/mm/yyyy') + 1"+
		     " AND b.area_id IN ("+mstrArea_id+")"+
	  " GROUP BY   b.area_id),0) number_ok"+
	  " FROM   area c"+
	  " WHERE   c.area_id IN ("+mstrArea_id+"))";

}
    public String BuildSQLAll(){
	return SQL = " select fullname,center_code ,number_trans, number_ok,case "+
		  " when number_trans = '0' then '0.00%' "+
		  " else TO_CHAR(((number_ok / number_trans) * 100),'999.99') || '%' "+
		  " end per from (SELECT   fullname, "+
		  " center_code, "+
		  " nvl((  SELECT   get_sum_map (b.area_id, "+
		   " TO_DATE (?, 'dd/mm/yyyy'),"+
		   " TO_DATE (?, 'dd/mm/yyyy'))"+
		   " FROM   isdn a, area_map_isdn b"+
		   " WHERE       a.reg_status = 1"+
		     " AND b.area_id = c.area_id"+
		     " AND a.imsi LIKE b.imsi_prefix"+
		     " AND b.start_date <= TRUNC (a.reg_date)"+
		     " AND (b.finish_date >= TRUNC (a.reg_date)"+
		     " OR b.finish_date IS NULL)"+
		     " AND TRUNC (a.reg_date) >="+
			    " TO_DATE (?, 'dd/mm/yyyy')"+
		     " AND TRUNC (a.reg_date) <"+
			    " TO_DATE (?, 'dd/mm/yyyy') + 1"+
	  " GROUP BY   b.area_id),0) number_trans,"+
	 " nvl((  SELECT   COUNT ( * )"+
	      " FROM   isdn a, area_map_isdn b"+
	     " WHERE       a.reg_status = 1"+
	     " AND b.area_id = c.area_id"+
		     " AND a.imsi LIKE b.imsi_prefix"+
		     " AND b.start_date <= TRUNC (a.reg_date)"+
		     " AND (b.finish_date >= TRUNC (a.reg_date)"+
		     " OR b.finish_date IS NULL)"+
		     " AND TRUNC (a.reg_date) >="+
			    " TO_DATE (?, 'dd/mm/yyyy')"+
		     " AND TRUNC (a.reg_date) <"+
			    " TO_DATE (?, 'dd/mm/yyyy') + 1"+
	  " GROUP BY   b.area_id),0) number_ok"+
	  " FROM   area c where parent_code !='0'"+
	  " ORDER BY center_code)";

    }
    public Vector ViewReport() throws Exception {
	try {
	   open();
	  String strSQL = BuildSQL();
	   stmt = mcnMain.prepareStatement(strSQL);
	   stmt.setString(1, mstrStartdate);
	    stmt.setString(2, mstrFinishdate);
	    stmt.setString(3, mstrStartdate);
	    stmt.setString(4, mstrFinishdate);
	    stmt.setString(5, mstrStartdate);
	    stmt.setString(6, mstrFinishdate);
	   return Database.convertToVector(stmt.executeQuery());
       } finally {
	   close();
       }

    }
    public Vector ViewReportAll() throws Exception {
       try {
	  open();
	 String strSQL = BuildSQLAll();
	  stmt = mcnMain.prepareStatement(strSQL);
	  stmt.setString(1, mstrStartdate);
          stmt.setString(2, mstrFinishdate);
          stmt.setString(3, mstrStartdate);
          stmt.setString(4, mstrFinishdate);
          stmt.setString(5, mstrStartdate);
          stmt.setString(6, mstrFinishdate);

	  return Database.convertToVector(stmt.executeQuery());
      } finally {
	  close();
      }

   }

/////
    public void Report() throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            open();

            String vstrFilePath = AppServer.getParam("ReportOut");
            String vstrTemplatePath = AppServer.getParam("ReportTemplate");
            String strTemplateFile = "";
            strTemplateFile = (new StringBuilder()).append(vstrTemplatePath).
                              append(mstrTemplate).toString();
            String strNameFile = mstrTemplate.substring(0,
                    mstrTemplate.length() - 4);
            String strFileNameOut = strNameFile + "_" +
                                    new java.text.
                                    SimpleDateFormat("yyMMddHHmmss").format(new
                    java.util.Date()) +
                                    ".xls";
            String strOutFileName = (new StringBuilder()).append(vstrFilePath).
                                    append(strFileNameOut).toString();
	    BuildSQL();
            stmt = mcnMain.prepareStatement(SQL);
            stmt.setString(1, mstrStartdate);
            stmt.setString(2, mstrFinishdate);
            stmt.setString(3, mstrStartdate);
            stmt.setString(4, mstrFinishdate);
	    stmt.setString(5, mstrStartdate);
            stmt.setString(6, mstrFinishdate);
            rs = stmt.executeQuery();
            Report report = new Report(rs, strTemplateFile, strOutFileName);
            report.setParameter("$from_date", mstrStartdate);
            report.setParameter("$to_date", mstrFinishdate);
	    report.setParameter("$City",mstrCity);
            report.fillDataToExcel();
            response.setString("Out.strFileNameOut",
                               strFileNameOut);
            response.setString("strURL", vstrFilePath);

        } finally {
            close();
        }

    }
    public void ReportAll() throws Exception {
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    try {
		open();

		String vstrFilePath = AppServer.getParam("ReportOut");
		String vstrTemplatePath = AppServer.getParam("ReportTemplate");
		String strTemplateFile = "";
		strTemplateFile = (new StringBuilder()).append(vstrTemplatePath).
				  append(mstrTemplate).toString();
		String strNameFile = mstrTemplate.substring(0,
			mstrTemplate.length() - 4);
		String strFileNameOut = strNameFile + "_" +
					new java.text.
					SimpleDateFormat("yyMMddHHmmss").format(new
			java.util.Date()) +
					".xls";
		String strOutFileName = (new StringBuilder()).append(vstrFilePath).
					append(strFileNameOut).toString();
//		SQL = " SELECT   fullname,center_code," +
//		      " number_trans," +
//		      " number_ok," +
//		      " TO_CHAR(( (number_ok / number_trans) * 100),'999.99') || '%' per" +
//		      " FROM   (  SELECT   b.area_id, c.fullname, c.center_code," +
//		      " get_sum_map (b.area_id," +
//		      " TO_DATE (?, 'dd/mm/yyyy')," +
//		      " TO_DATE (?, 'dd/mm/yyyy'))" +
//		      " number_trans," +
//		      " COUNT ( * ) number_ok" +
//		      " FROM   isdn a, area_map_isdn b, area c" +
//		      " WHERE       a.reg_status = 1" +
//		      " AND b.area_id = c.area_id" +
//		      " AND a.imsi LIKE b.imsi_prefix" +
//		      " AND b.start_date <= TRUNC (a.reg_date)" +
//		      " AND (b.finish_date >= TRUNC (a.reg_date)" +
//		      " OR b.finish_date IS NULL)" +
//		      " AND TRUNC (a.reg_date) >=" +
//		      " TO_DATE (?, 'dd/mm/yyyy')" +
//		      " AND TRUNC (a.reg_date) <" +
//		      " TO_DATE (?, 'dd/mm/yyyy') + 1" +
//		      " GROUP BY   b.area_id, c.fullname,c.center_code)"+
//		      " ORDER BY   center_code";
		String SQL = BuildSQLAll();
		stmt = mcnMain.prepareStatement(SQL);
		stmt.setString(1, mstrStartdate);
		stmt.setString(2, mstrFinishdate);
		stmt.setString(3, mstrStartdate);
		stmt.setString(4, mstrFinishdate);
		stmt.setString(5, mstrStartdate);
		stmt.setString(6, mstrFinishdate);
		rs = stmt.executeQuery();
		Report report = new Report(rs, strTemplateFile, strOutFileName);
		report.setParameter("$from_date", mstrStartdate);
		report.setParameter("$to_date", mstrFinishdate);
		report.setParameter("$City",mstrCity);
		report.fillDataToExcel();
		response.setString("Out.strFileNameOut",
				   strFileNameOut);
		response.setString("strURL", vstrFilePath);

	    } finally {
		close();
	    }

    }

    public void loadParameter() {
        mstrCity = StringUtil.nvl(request.getString("mstrCity"), "");
        mstrStartdate = StringUtil.nvl(request.getString("mstrStartdate"), "");
        mstrFinishdate = StringUtil.nvl(request.getString("mstrFinishdate"), "");
        mstrTemplate = StringUtil.nvl(request.getString("In.Template"), "");
        mstrArea_id = StringUtil.nvl(request.getString("mstrArea_id"), "");
    }

    protected String getModuleName() {
        return PermissionApp.FormReport;
    }

}
